package cn.edu.seu.herald.informationplatform.dao.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

import com.mysql.jdbc.Statement;

import cn.edu.seu.herald.informationplatform.bean.league.Message;

public class MessageDao extends DataAccessObject{
private static MessageDao daoInstance=null;
	
	//Constructor of LeagueDao
	protected MessageDao(){
		
	}
	
	//Get LeagueDao instance
	public static MessageDao getDao(){
		if(daoInstance==null){
			daoInstance=new MessageDao();
		}
		return daoInstance;
	}
	
	//add
	public int addMessage(Message message) throws SQLException {
		Connection con = getConnectionLeague();
		try {
			PreparedStatement ps = con.prepareStatement("insert into lg_message(activity_id,league_id,recruit_id,message_id,content,user_or_league_id,time,user_or_league) values(?,?,?,?,?,?,?,?)",Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1,message.getActivityId());
			ps.setInt(2,message.getLeagueId());
			ps.setInt(3,message.getRecruitId());
			ps.setInt(4,Message.NO_SUB_MESSAGE);
			ps.setString(5,message.getContent());
			ps.setInt(6,message.getUserOrLeagueId());
			ps.setTimestamp(7,message.getTime());
			ps.setString(8,message.getUserOrLeague());
			ps.execute();
			ResultSet rsResultSet=ps.getGeneratedKeys();
			int new_id=-1;
			if(rsResultSet.next()){
				new_id=rsResultSet.getInt(1);
			}
			closeRs(rsResultSet);
			return new_id;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	//delete
	public void deleteMessage(int id) throws SQLException{
		Connection con = getConnectionLeague();
		try {
			PreparedStatement ps = con.prepareStatement("delete from lg_message where id=?");
			ps.setInt(1,id);
			ps.executeUpdate();	
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	//get
	public Map<Integer , Message> getMessageFromActivityId(int id) throws SQLException{
		Connection con = getConnectionLeague();
		LinkedHashMap< Integer,Message> mes = new LinkedHashMap<Integer, Message>();
		try {
			PreparedStatement ps = con.prepareStatement("select * from lg_message where activity_id=? order by time asc limit 50;");
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Message message = new Message();
				message.setActivityId(rs.getInt(2));
				message.setLeagueId(rs.getInt(3));
				message.setRecruitId(rs.getInt(4));
				message.setSubMessageId(rs.getInt(5));
				message.setContent(rs.getString(6));
				message.setUserOrLeagueId(rs.getInt(7));
				message.setTime(rs.getTimestamp(8));
				message.setUserOrLeague(rs.getString(9));
				mes.put(rs.getInt(1), message);
			}
			closeRs(rs);
			return mes;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	
	public Map<Integer , Message> getMessageFromLeagueId(int id) throws SQLException{
		Connection con = getConnectionLeague();
		LinkedHashMap< Integer,Message> mes = new LinkedHashMap<Integer, Message>();
		try {
			PreparedStatement ps = con.prepareStatement("select * from lg_message where league_id=? order by time asc");
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Message message = new Message();
				message.setActivityId(rs.getInt(2));
				message.setLeagueId(rs.getInt(3));
				message.setRecruitId(rs.getInt(4));
				message.setSubMessageId(rs.getInt(5));
				message.setContent(rs.getString(6));
				message.setUserOrLeagueId(rs.getInt(7));
				message.setTime(rs.getTimestamp(8));
				message.setUserOrLeague(rs.getString(9));
				mes.put(rs.getInt(1), message);
			}
			closeRs(rs);
			return mes;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
	public Map<Integer , Message> getMessageFromRecruitId(int id) throws SQLException{
		Connection con = getConnectionLeague();
		LinkedHashMap< Integer,Message> mes = new LinkedHashMap<Integer, Message>();
		try {
			PreparedStatement ps = con.prepareStatement("select * from lg_message where recruit_id=?");
			ps.setInt(1,id);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				Message message = new Message();
				message.setActivityId(rs.getInt(2));
				message.setLeagueId(rs.getInt(3));
				message.setRecruitId(rs.getInt(4));
				message.setSubMessageId(rs.getInt(5));
				message.setContent(rs.getString(6));
				message.setUserOrLeagueId(rs.getInt(7));
				message.setTime(rs.getTimestamp(8));
				message.setUserOrLeague(rs.getString(9));
				mes.put(rs.getInt(1), message);
			}
			closeRs(rs);
			return mes;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
		finally{
			closeCon(con);
		}
	}
}
